using System;
using System.Data;
using System.Data.Odbc;
using System.Collections;
using System.Collections.Generic;
using System.Data.SQLite;

namespace DocLibApp
{
	/// <summary>
    /// SQLite Database Access Library
	/// 
	/// copyright(c) 2008. Min Liang Huang
	/// 
	/// A facility class use for easier access of ADO functions handling SQLite Database
	/// 
	/// 
	/// 
	/// 
	/// </summary>
	/// 
	/// <log>
	/// 5/18/2008: started
	/// </log>
	/// <remarks>
    /// 
    /// /* INSERT INTO SYSTEM_LIST ([NAME], [DESC], [LIST_GROUP], [DT_CREATE]) VALUES
    /// ('TEST2','TEST 1 DESCRIPT','TST_GROUP', '2008-05-19T23:04:44') */
    /// select DATETIME([DT_CREATE]) from SYSTEM_LIST;
    /// select *, DATETIME([DT_CREATE]) from SYSTEM_LIST ORDER BY [DT_CREATE] DESC
    /// 
	/// Connection String for SQLite:
    /// "Data Source=test.db3;Pooling=False";
    /// 
    /// - SQLite handles unicode string well. Even though it looks strange in SQLite Admin program, when it's exported, the right chinese
    /// - characters are exported correctly if you input it by C#. (UseUTF16Encoding=False needs to be in connection string)
    /// - table name, field name in SQLite is case sensitive
    /// - group is a reserved keyword in SQL, don't use it in field name
    /// - To use reserved keyword in a name is enclose the keyword in [Keyword], or 'Keyword' or "Keyword"
	/// </remarks>
	public class SQLiteDB
	{
		#region My Variables

        //definition		
        private string Pattern_ConnectionString = "Data Source=%FILENAME%;Pooling=False;UseUTF16Encoding=False";
        private string Pattern_ConnectionString_Password = "Data Source=%FILENAME%;Pooling=False;UseUTF16Encoding=False;Password=%PASSWORD%";

        private string DBFileName = "";
		private string dbConnectionString = "";

		/// <summary>
		/// Hash table to store table and its select statements
		/// </summary>
		private System.Collections.Hashtable htTable = new System.Collections.Hashtable();

		private System.Data.SQLite.SQLiteConnection myConnection;

		#endregion // My variables
		
		#region Constructor
		/// <summary>
		/// 
		/// </summary>
		/// <remarks>
		/// tested
		/// </remarks>
		/// <param name="DBFileName"></param>
		public SQLiteDB(string DBFileName)
		{
			if (!String.IsNullOrEmpty(DBFileName))
			{
				this.DBFileName = DBFileName;
                this.dbConnectionString = Pattern_ConnectionString.Replace("%FILENAME%", DBFileName);
			}            
		}

        /// <summary>
        /// create a new SQLite connection, with filename and database file password
        /// </summary>
        /// <param name="DBFileName"></param>
        /// <param name="Password"></param>
        public SQLiteDB(string DBFileName, string Password)
        {
            if (!string.IsNullOrEmpty(DBFileName) && !string.IsNullOrEmpty(Password))
            {
                this.DBFileName = DBFileName;
                this.dbConnectionString = Pattern_ConnectionString_Password.Replace("%FILENAME%", DBFileName).Replace("%PASSWORD%", Password);
            }
        }
		#endregion // Constructor

		#region Public Functions
        #region System Functions
        /// <summary>
        /// query internal SQLite Last created Identity for primary key
        /// </summary>
        /// <returns></returns>
        public string GetLastUseID()
        {
            string sql = "SELECT last_insert_rowid()";
            return GetDataString(sql);
        }

        /// <summary>
        /// get a list of Primary Key fields in a table
        /// </summary>
        /// <param name="table_name"></param>
        /// <returns></returns>
        public List<string> GetPrimaryKeyFieldsForTable(string table_name)
        {
            List<string> PrimaryKeyFields = new List<string>();

            DataTable dtSchema = GetSchema_Table(table_name);

            foreach (DataRow dr in dtSchema.Rows)
            {
                string FieldName = ("" + dr["COLUMN_NAME"]).Trim();
                string PrimaryKey = ("" + dr["PRIMARY_KEY"]).Trim();

                if (!string.IsNullOrEmpty(PrimaryKey) && PrimaryKey.ToUpper() == "TRUE")
                    PrimaryKeyFields.Add(FieldName);
            }

            return PrimaryKeyFields;
        }

        /// <summary>
        /// return arrayList of column of a DataTable
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public System.Collections.ArrayList GetColumnsFromTable(System.Data.DataTable dt)
        {
            System.Collections.ArrayList alist = new ArrayList();
            foreach (System.Data.DataColumn dc in dt.Columns)
            {
                alist.Add(dc);
            }

            return alist;
        }

        /// <summary>
        /// Returns the DataTable object of all the tables in the database
        /// </summary>
        /// <returns>ArrayList of DataTable</returns>
        public System.Collections.ArrayList GetDataTableStructureForAllTables()
        {
            System.Collections.ArrayList DataTableList = new ArrayList();

            System.Collections.ArrayList tablelist = GetListofTables();

            if (tablelist.Count > 0)
            {
                foreach (object obj in tablelist)
                {
                    string tablename = obj.ToString();
                    System.Data.DataTable dt = GetDataTable("SELECT * FROM " + tablename + " WHERE 1 = -1", tablename);
                    DataTableList.Add(dt);
                }
            }

            return DataTableList;
        }

        /// <summary>
        /// get a list of columns of a table (ht[column name] = type)
        /// </summary>
        /// <remarks> GetSchema(NAME, Restriction)
        /// List of name:
        /// MetaDataCollections, DataSourceInformation
        /// DataTypes, Restrictions, ReservedWords, Columns, Indexes
        /// Procedures, Tables, Views
        /// </remarks>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public System.Collections.Hashtable GetTableColumnInfo(string tablename)
        {
            System.Collections.Hashtable ht = new Hashtable();

            OpenConnection();

            string[] restriction = { null, null, tablename, null };

            //COLUMN_NAME, DATA_TYPE
            System.Data.DataTable dt = myConnection.GetSchema("Columns", restriction);

            foreach (DataRow dr in dt.Rows)
            {
                string ColumnName = dr["COLUMN_NAME"].ToString();
                string ColumnType = dr["DATA_TYPE"].ToString();

                ht[ColumnName] = ColumnType;
            }

            return ht;
        }

        /// <summary>
        /// get schema information about a table in SQLite
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public DataTable GetSchema_Table(string tablename)
        {
            string[] restriction = { null, null, tablename, null };
            OpenConnection();
            DataTable dt = myConnection.GetSchema("COlumns", restriction);

            return dt;
        }

        public System.Data.DataTable GetSchema()
        {
            System.Data.DataTable dt = myConnection.GetSchema("Columns");
            return dt;
        }
        /// <summary>
        /// Encrypt an un-encrypted database file, the database file will be encrypted after the function
        /// </summary>
        /// <param name="password"></param>
        public void EncryptDatabase(string password)
        {
            //To encrypt an un-encrypted database file
            //first open the connection to the database file
            OpenConnection();

            myConnection.ChangePassword(password);
        }

        /// <summary>
        /// Decrypt an encrypted database file, the file will be decrypted after the function
        /// </summary>
        /// <param name="password"></param>
        public void DecrytDatabase(string password)
        {
            OpenConnection();
            myConnection.ChangePassword(""); //set the password to null effectively removed the encryption
        }

        /// <summary>
        /// compact / vaccum the sqlite database file
        /// </summary>
        public void CompactDatabase()
        {
            OpenConnection();

            ExecuteSQL("vacuum;");
        }

        /// <summary>
        /// get a list of user tables in database
        /// </summary>
        /// <returns></returns>
        public System.Collections.ArrayList GetListofTables()
        {
            System.Collections.ArrayList alist = new System.Collections.ArrayList();

            //excluse sqlite system tables in the list
            string sql = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'SQLite%' ORDER BY name";
            System.Collections.ArrayList list = GetRowsWithDataReader(sql, false, false);

            foreach (Object obj in list)
            {
                if (obj is List<string>)
                {
                    string name = (obj as List<string>)[0];
                    alist.Add(name);
                }
            }

            return alist;
        }

        public int GetRecordCount(string table)
        {
            string sql = "SELECT count(*) FROM " + table;
            string res = "0";

            SQLiteCommand oc = new SQLiteCommand(sql, myConnection);
            SQLiteDataReader odr = oc.ExecuteReader();
            while (odr.Read())
            {
                res = odr.GetString(0).Trim();
            }

            return Int32.Parse(res);
        }
        #endregion //EO System Functions

        #region SQL Command
        /// <summary>
		/// Execute a SQL command (such as DELETE, update etc
		/// </summary>
		/// <example>
		/// oracle_odp odp = new oracle_odp();
		/// odp.ExecuteSQL("update table set field1='value1' where id = 344");
		/// </example>
		/// 12/26/2006: tested
		/// <param name="sql">sql command</param>
		public string ExecuteSQL(string sql)
		{
			int i = -1;
			try
			{
				OpenConnection();

                SQLiteCommand oc = new SQLiteCommand(sql, myConnection);
			
				i= oc.ExecuteNonQuery();
			}
			catch (System.Exception ex)
			{
                HandleException(ex);
                return ex.Message;
			}
			finally
			{

			}

            return sql + System.Environment.NewLine + " is executed. (return code = " + i.ToString() + ")";
		}

        /// <summary>
        /// Check if a record would found base on the query
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool IfRecordExistUseCriteria(string sql)
        {
            string res = "";

            if (!string.IsNullOrEmpty(sql))
                res = GetDataString(sql);

            if (!string.IsNullOrEmpty(res))
                return true;

            return false;
        }

        public int GetRecordCountUseCriteria(string sql)
        {
            int count = 0;

            OpenConnection();

            SQLiteCommand oc = new SQLiteCommand(sql, this.myConnection);

            SQLiteDataReader odr = oc.ExecuteReader();

            while (odr.Read())
            {
                count = odr.GetInt32(0);
            }

            CloseConnection();

            return count;
        }

        public string GetDataString(string sql)
        {
            string res = "";

            SQLiteCommand oc = new SQLiteCommand(sql, this.myConnection);

            SQLiteDataReader odr = oc.ExecuteReader();

            while (odr.Read())
            {
                res = odr.GetValue(0).ToString().Trim();
            }

            return res;
        }

        /// <summary>
        /// retrieves 1st row of a matching record from sql query, in fieldname = value in a hash table
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public System.Collections.Hashtable GetRowWithDataReaderIntoHash(string sql)
        {
            System.Collections.Hashtable ht = new Hashtable();

            SQLiteDataReader odr = null;

            OpenConnection();

            SQLiteCommand oc = new SQLiteCommand(sql, myConnection);
            odr = oc.ExecuteReader();

            while (odr.Read())
            {
                for (int i = 0; i < odr.FieldCount; i++)
                {
                    string field = odr.GetName(i);
                    string value = odr.GetValue(i).ToString();

                    ht[field] = value;
                }
            }

            odr.Close();

            return ht;
        }

        /// <summary>
        /// Retrieve access data table values from a table into a Array List of strings using Oracle Data Reader
        /// </summary>
        /// <remarks>
        /// Use for performance gain, faster than ADO DataSet
        /// </remarks>
        /// <param name="sql">select SQL statement</param>
        /// <param name="skipBlankValue">Don't add blank (null) values to the ArrayList</param>
        /// <returns>ArrayList of string contains result values</returns>
        public System.Collections.ArrayList GetRowsWithDataReader_IncludeColumnDescription(string sql, string tablename, string keyfield)
        {
            bool bFieldNameAdded = false;

            System.Collections.ArrayList alist = new System.Collections.ArrayList();

            SQLiteDataReader odr = null;

            try
            {
                OpenConnection();

                SQLiteCommand oc = new SQLiteCommand(sql, myConnection);
                odr = oc.ExecuteReader();

                while (odr.Read())
                {
                    List<string> aRow = new List<string>();

                    if (!bFieldNameAdded)
                    {
                        List<string> FieldList = new List<string>();

                        for (int k = 0; k < odr.FieldCount; k++)
                        {
                            string fieldname = odr.GetName(k).Trim();

                            FieldList.Add(fieldname);
                        }

                        alist.Add(FieldList);
                        bFieldNameAdded = true;
                    }

                    for (int i = 0; i < odr.FieldCount; i++)
                    {
                        string val = odr.GetValue(i).ToString().Trim();

                        if (string.IsNullOrEmpty(val))
                            val = "<NULL>";

                        aRow.Add(val);
                    }

                    alist.Add(aRow);
                }
            }
            catch (System.Exception ex)
            {
                HandleException(ex);
            }
            finally
            {
                odr.Close();
            }

            return alist;
        }

        /// <summary>
        /// Retrieve access data table values from a table into a Array List of strings using Oracle Data Reader
        /// </summary>
        /// <remarks>
        /// Use for performance gain, faster than ADO DataSet
        /// </remarks>
        /// <param name="sql">select SQL statement</param>
        /// <param name="skipBlankValue">Don't add blank (null) values to the ArrayList</param>
        /// <returns>ArrayList of string contains result values</returns>
        public System.Collections.ArrayList GetRowsWithDataReader(string sql, bool skipBlankValue, bool IncludeFieldName)
		{
			bool bFieldNameAdded = false;

			System.Collections.ArrayList alist = new System.Collections.ArrayList();

			SQLiteDataReader odr = null;

			try
			{
				OpenConnection();

                SQLiteCommand oc = new SQLiteCommand(sql, myConnection);
                odr = oc.ExecuteReader();

				while(odr.Read())
				{
                    List<string> aRow = new List<string>();

					if (IncludeFieldName && !bFieldNameAdded)
					{
                        List<string> FieldList = new List<string>();

						for (int k = 0; k < odr.FieldCount; k++)
						{
							string fieldname = odr.GetName(k).Trim();

                            FieldList.Add(fieldname);
						}

                        alist.Add(FieldList);
						bFieldNameAdded = true;
					}

					for (int i = 0;i < odr.FieldCount; i++)
					{
						string val = odr.GetValue(i).ToString().Trim();

						if (skipBlankValue && val.Trim() == "")
							break;
						else
						{
                            if (String.IsNullOrEmpty(val))
                                val = "<NULL>";

                            aRow.Add(val);
						}
					}

					alist.Add(aRow);
				}
			}
			catch (System.Exception ex)
			{
				HandleException(ex);
			}
			finally
			{
				odr.Close();
			}

			return alist;
		}
		#endregion // SQL Command

        #region Data Table
        /// <summary>
        /// Retrieve oracle data table use select sql statement
        /// </summary>
        /// <remarks>
        /// tested
        /// </remarks>
        /// <param name="sSelectSQL">select sql statement</param>
        /// <param name="tableName">table name</param>
        /// <returns>System.Data.DataTable</returns>
        public System.Data.DataTable GetDataTable(string sSelectSQL, string tableName)
        {
            System.Data.DataTable dt = new DataTable();
            try
            {
                SetTable(tableName, sSelectSQL);

                System.Data.DataSet ds = new System.Data.DataSet();

                SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(sSelectSQL, myConnection);
                SQLiteCommandBuilder myCommandBuilder = new SQLiteCommandBuilder(myAdapter);
                myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                myAdapter.Fill(ds, tableName);

                dt = ds.Tables[tableName];
            }
            catch (System.Exception ex)
            {
                HandleException(ex);
            }

            return dt;
        }
        #endregion //EO Data Table

        #region Export Data
        /// <summary>
        /// Retrieve a table from SQLite and then export it to CSV file
        /// </summary>
        /// <param name="table_name"></param>
        /// <param name="outputfilename"></param>
        public void ExportDataTableToCSV(string table_name, string outputfilename)
        {
            DataTable dt = GetDataTable("SELECT * FROM " + table_name, table_name);
            if (dt.Rows.Count > 0)
            {
                util.DataTableToCSVFile(dt, outputfilename);
            }
        }

        /// <summary>
        /// Export datatable into XML file with structure in XML
        /// </summary>
        /// <param name="table_name"></param>
        /// <param name="outputfilename"></param>
        public void ExportDataTableToXML(string table_name, string outputfilename)
        {
            DataTable dt = GetDataTable("SELECT * FROM " + table_name, table_name);
            if (dt.Rows.Count > 0)
            {
                dt.WriteXml(outputfilename, true);
            }
        }
        #endregion

        public void Test()
        {
            OpenConnection();

            byte[] stuff = util.ReadFileIntoByteArray(@"c:\test.ico");

            SQLiteCommand myc = new SQLiteCommand("INSERT INTO SYSTEM_ICON ([NAME],[ICON]) VALUES (@NAME, @ICON)", myConnection);

            myc.Parameters.Add("@NAME", System.Data.DbType.String, 20).Value = "test8";
            myc.Parameters.Add("@ICON", System.Data.DbType.Binary, stuff.Length).Value = stuff;

            myc.ExecuteNonQuery();            
        }

        #endregion //Public Functions

        #region Public Static Function

        /// <summary>
        /// create a new SQLite blank database file
        /// </summary>
        /// <param name="filename"></param>
        /// <returns></returns>
        public static bool CreateSQLiteDatabaseFile(string filename)
        {
            string res = "";
            if (System.IO.File.Exists(filename))
            {
                res = "The file (" + filename + ") already exist.";
                return false;
            }
            else
            {
                SQLiteConnection.CreateFile(filename);

                if (System.IO.File.Exists(filename))
                {
                    res = "The file (" + filename + ") created successfully.";
                    return true;
                }
                else
                {
                    res = "The file (" + filename + ") could not be created.";
                    return false;
                }
            }
        }

        /// <summary>
        /// Convert a DateTime into a string that is suitable for insert to SQLite insert statement
        /// 2008-05-19T23:04:44
        /// tested
        /// </summary>
        /// <param name="aDateTime"></param>
        /// <returns></returns>
        public static string DateTimeToString(System.DateTime aDateTime)
        {
            //2008-05-19T23:04:44
            //string Pattern = "YYYY-MM-DDTHH:MM:SS";
            string YYYY = "", MM = "", DD = "", HH = "", mm = "", SS = "";
            YYYY = aDateTime.Year.ToString();
            MM = aDateTime.Month.ToString().PadLeft(2, '0');
            DD = aDateTime.Day.ToString().PadLeft(2, '0');
            HH = aDateTime.Hour.ToString().PadLeft(2, '0');
            mm = aDateTime.Minute.ToString().PadLeft(2, '0');
            SS = aDateTime.Second.ToString().PadLeft(2, '0');

            string res = YYYY + "-" + MM + "-" + DD + "T" + HH + ":" + mm + ":" + SS;

            return res;
        }

        /// <summary>
        /// Parse a standard SQLite DateTime String into System.DateTime format
        /// tested
        /// </summary>
        /// <param name="str">2008-05-19T23:04:44</param>
        /// <returns></returns>
        public static System.DateTime StringToDateTime(string str)
        {
            System.DateTime value = new DateTime();

            if (!string.IsNullOrEmpty(str))
            {
                value = System.DateTime.Parse(str);
            }

            return value;
        }

        /// <summary>
        /// POPULATE DT_CREATE, DT_UPDATE field automatically for hash
        /// </summary>
        /// <param name="ht"></param>
        public static void AddDateDefaultToHash(ref System.Collections.Hashtable ht)
        {
            ht["DT_CREATE"] = SQLiteDB.DateTimeToString(System.DateTime.Now);
            ht["DT_UPDATE"] = SQLiteDB.DateTimeToString(System.DateTime.Now);
        }

        #endregion //EO Public Static Function

        #region Private Functions
        public void OpenConnection()
		{
			try
			{
                if (this.myConnection == null)
                    this.myConnection = new SQLiteConnection(dbConnectionString);

				if (this.myConnection.State == ConnectionState.Closed)
					this.myConnection.Open();
			}
			catch (System.Exception ex)
			{
				HandleException(ex);
			}
		}

		public void CloseConnection()
		{
			try
			{
				if (myConnection != null && myConnection.State == System.Data.ConnectionState.Open)
					myConnection.Close();
			}
			catch (System.Exception ex)
			{
				HandleException(ex);
			}
		}

		private void HandleException(System.Exception ex)
		{
            eh myeh = new eh(true);
            myeh.WriteExpLog(ex);
		}

		private void print(string msg)
		{
			System.Diagnostics.Debug.WriteLine(msg, "Message");
		}

		/// <summary>
		/// stores the sql associate with table name
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="sql"></param>
		private void SetTable(string tableName, string sql)
		{
			htTable[tableName] = sql;
		}

		/// <summary>
		/// gets the associated sql query for a table name
		/// </summary>
		/// <param name="tableName">table name</param>
		/// <returns>sql query</returns>
		private string GetTable(string tableName)
		{
			return ("" + htTable[tableName]).Trim();
		}
		#endregion //Private Functions
	}
}
